-- mysql 版本
-- mysql> select version();
-- +-------------------------+
-- | version()               |
-- +-------------------------+
-- | 8.0.41-0ubuntu0.22.04.1 |

use data_ry;

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(64) DEFAULT NULL COMMENT '用户名称',
  `age` int(4) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

desc t_user;


INSERT INTO `t_user` (`id`, `username`, `age`)  VALUES ('userid_0', 18);



-- 新建一个账户的表
CREATE TABLE `t_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '账户',
  `balance` int(4) DEFAULT NULL COMMENT '余额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='账户表';


INSERT INTO `t_accounts` (`balance`)  VALUES (1000);



-- 事务 A
START TRANSACTION;
-- 开始交易;
SELECT balance FROM t_accounts WHERE id = 1;  -- 读取账户余额
 
-- 事务 B
START TRANSACTION;
-- 修改账户余额
UPDATE t_accounts SET balance = 5000 WHERE id = 1;  

COMMIT;
 
-- 事务 A
 -- 可能会读到事务 B 尚未提交的余额
SELECT balance FROM t_accounts WHERE id = 1; 

-- 查看当前会话的事务隔离级别
select @@transaction_isolation;
-- 查看当前系统的事务隔离级别
select @@global.transaction_isolation;


show  variables like 'transaction_isolation';
-- repeatable read 可重复复


-- 幻读演示
-- 准备两条数据
INSERT INTO `t_accounts` (`balance`)  VALUES (1000);
INSERT INTO `t_accounts` (`balance`)  VALUES (3000);
INSERT INTO `t_accounts` (`balance`)  VALUES (5000);

select *  from t_accounts where balance > 3000;


-- 事务 A
begin;
START TRANSACTION;
-- 读取余额大于3000的账户
SELECT * FROM t_accounts WHERE balance > 3000;  
 
-- 事务 B
begin;
START TRANSACTION;
-- 插入一个符合条件的新账户
INSERT INTO t_accounts (balance) VALUES (8000);
SELECT * FROM t_accounts;
COMMIT;
 
-- 事务 A
-- 结果集中可能会多出一个账户
SELECT * FROM t_accounts WHERE balance > 3000;



-- 实验2 插入数据的速度测试
-- 插入100w条数据
-- demo.py的run4方法，生产了一个sql文件可以插入100w条数据
-- 查询性能测试
-- source /home/fhj/code/alembic_demo/test_mysql/insert_data.sql
-- 导入测试数据
-- Query OK, 1000000 rows affected (13.89 sec)
-- Records: 1000000  Duplicates: 0  Warnings: 0
-- 主键默认是走索引的，点查
select *  from t_example where id = 1099;
-- 主键默认是不走索引的
select *  from t_example where name = '李雪';
-- 查看执行计划
explain select *  from t_example where id = 1099;

explain select *  from t_example where name = '李雪';
explain select *  from t_example where name = '李雪' and id = 1099;
-- 执行计划和 第一条sql的执行计划一直，说明执行计划被优化过了


select * from t_example where name like '李雪';
select * from t_example where name like '%李雪';
-- 查看当前有几个客户端连接过来
-- https://blog.csdn.net/nibonnn/article/details/118945976
SELECT host, state FROM information_schema.processlist;

-- mysql 100万条数据的一张表，如何查询优化？
-- https://blog.csdn.net/feikudai8460/article/details/112515434

select max(id) from t_example;

select * from t_example order by id desc limit 0,10;
select * from t_example order by id desc limit 9999,10;
select * from t_example order by id desc limit 99999,10;
select * from t_example order by id desc limit 999999,10;